import io

from flask import render_template, jsonify, request
from sqlalchemy import text

from tzq.extensions import db
from tzq.sql_file_analysis_tool import sfat_bp
from tzq.sql_file_analysis_tool.sfat_model import UploadedFile
from tzq.util.ExecuteBatchSQLScripts import ExecuteBatchSQLScripts


# 首页

@sfat_bp.route('/')
def dcs_index():
    return render_template('sfat_index.html')


@sfat_bp.route('/test', methods=['GET', 'POST'])
def test():
    return render_template('test.html')


@sfat_bp.route('/upload', methods=['POST'])
def upload_file():
    if 'file' not in request.files:
        return jsonify(code=1, msg="未选择文件")

    file = request.files['file']
    file_size = 0
    if file:
        # 将文件指针移动到文件末尾，获取位置（即大小）
        file.stream.seek(0, io.SEEK_END)
        file_size = file.stream.tell()
        file.stream.seek(0)  # 重置指针位置（避免后续操作出错）

    if file.filename == '':
        return jsonify(code=1, msg="无效文件名")

    if file and allowed_file(file.filename):
        filename = file.filename
        # 读取文件内容
        content = file.read().decode('utf-8')
        # 创建数据库记录
        new_file = UploadedFile(
            filename=filename,
            content=content,
            # file_size=os.path.getsize(file)
            file_size=file_size
        )
        db.session.add(new_file)
        db.session.commit()
        return jsonify(code=0, msg="上传成功")
    return jsonify(code=1, msg="文件类型不支持")


def allowed_file(filename):
    return '.' in filename and \
        filename.rsplit('.', 1)[1].lower() in {'txt', 'py', 'java', 'js', 'css', 'sql', 'json'}


@sfat_bp.route('/codeView', methods=['GET', 'POST'])
def code_view():
    if request.method == "GET":
        id_string = request.args.get("id")
        uploadedFile = UploadedFile.query.get_or_404(str(id_string))
    return render_template('code_view.html', content=uploadedFile.content)


@sfat_bp.route('/api/getCodeView/<int:id>', methods=['GET'])
def get_code_view(id):
    try:
        uploadedFile = UploadedFile.query.get_or_404(id)
        return jsonify({"code": 0, "msg": "", "data": uploadedFile.to_dict()})
    except Exception as e:
        return jsonify({"code": -1, "msg": str(e)}), 500


@sfat_bp.route('/codeView2', methods=['GET', 'POST'])
def code_view2():
    if request.method == "GET":
        v_id = request.args.get("id")
        v_sql_num = request.args.get("sql_num")
        v_record_sql = text(
            """
            select sql_detail, line_numbers
            from sql_file_analysis_tool_t
            where 1 = 1
              and id = :v_id
              and sql_num = :v_sql_num
            """)
        # 拿到分页记录
        results = db.session.execute(v_record_sql, {
            'v_id': v_id,
            'v_sql_num': v_sql_num
        }).fetchone()
        results2 = results._asdict()
        v_sql_detail = str(results2['sql_detail'])
        v_line_numbers = str(results2['line_numbers'])
    return render_template('code_view2.html', content=v_sql_detail, line_numbers=v_line_numbers)


@sfat_bp.route('/api/initFileAnalysisData/', methods=['GET'])
def init_file_analysis_data():
    """
    初始化 文件分析 的相关信息
    """

    try:
        v_sql = """
                truncate table sql_file_analysis_tool_t;
                insert into sql_file_analysis_tool_t (id, file_name, file_content, sql_num, sql_type, line_numbers,
                                                      line_count, each_sql_content, sql_detail, table_name, index_name,
                                                      has_last_update_date, update_has_where_condition,
                                                      delete_has_where_condition, check_chinese_semicolon,
                                                      first_5_lines_has_search_path)
                select id,
                       file_name,
                       file_content,
                       sql_num,
                       sql_type,
                       line_numbers,
                       line_count,
                       each_sql_content,
                       sql_detail,
                       table_name,
                       index_name,
                       has_last_update_date,
                       update_has_where_condition,
                       delete_has_where_condition,
                       check_chinese_semicolon,
                       first_5_lines_has_search_path
                from sql_file_analysis_tool_v; 
                """
        executor = ExecuteBatchSQLScripts(db)
        v_result = executor.execute_sql_json(v_sql)
        v_result = v_result.get_json()
        if v_result['code'] == 1:
            return jsonify({"code": 1, "msg": "【OK】"})
        elif v_result['code'] == 0:
            return jsonify({"code": -1, "msg": "【ERROR】" + v_result['msg']})
    except Exception as e:
        return jsonify({"code": 0, "msg": str(e), "error": str(e)}), 500


# 批量删除
@sfat_bp.route('/api/uploadedFilesBatchDelete', methods=['POST'])
def uploaded_files_batch_delete():
    try:
        data = request.get_json()
        ids = data.get('ids', [])

        if not ids:
            return jsonify({'success': False, 'message': '未提供删除ID'}), 400

        # 使用事务执行批量删除
        deleted_count = db.session.query(UploadedFile) \
            .filter(UploadedFile.id.in_(ids)) \
            .delete(synchronize_session=False)

        db.session.commit()
        return jsonify({
            'success': True,
            'deleted_count': deleted_count,
            'message': f'成功删除{deleted_count}条记录'
        })

    except Exception as e:
        db.session.rollback()
        return jsonify({
            'success': False,
            'message': f'删除失败: {str(e)}'
        }), 500
